'''
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 读取Excel操作 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
'''
import enum
import openpyxl
from openpyxl import Workbook  # 导入新建excel文件的模块
from openpyxl import load_workbook  # 导入读取excel文件的模块


class TExcelError(Exception):
    def __init__(self, msg="未知错误"):
        self.msg = msg

    def __str__(self):
        return repr("[TExcelError] --> " + self.msg)

class TExcelScanDire(enum.Enum):
    kToRight = 0
    kToDown = 1
    kToLeft = 2
    kToUp = 3

class TExcel:
    # 初始化工作空间，传入excel文件路径
    file_path = ''

    def __init__(self, xl_path=''):
        try:  # 读取 excel
            self._xl_hander = load_workbook(xl_path, data_only=True)
            self.file_path = xl_path
            print(self._xl_hander.sheetnames)
        except FileNotFoundError:
            print("[{0}] not found".format(xl_path))
            raise

    def sheetNameArray(self):
        return self._xl_hander.sheetnames

    def sheet(self, sheet=''):
        if sheet not in self._xl_hander.sheetnames:
            # print(f"<{self.file_path} Error> Sheet:{sheet} does not exist")
            raise TExcelError(f"<{self.file_path} Error> Sheet:_ {sheet} _ does not exist")
        return self._xl_hander[sheet]

    def sheetACell(self, sheet='', row=0, col=0):  # 0 会报错
        sh = self.sheet(sheet)
        return sh.cell(row=row, column=col)

    def sheetAColumn(self, sheet='', col=0, start_row=1, count=1):
        result = []
        sh = self.sheet(sheet)

        if count >= 1 and start_row >= 1 and col >= 1:
            cnt = 0
            while cnt < count:
                result.append(sh.cell(start_row + cnt, col).value)
                cnt += 1

        return result

    def sheetARow(self, sheet='', row=0, start_col=1, count=1):
        result = []
        sh = self.sheet(sheet)

        if count >= 1 and start_col >= 1 and row >= 1:
            cnt = 0
            while cnt < count:
                result.append(sh.cell(row, start_col + cnt).value)
                cnt += 1

        return result

    # 从规定位置向单个方向扫描连续的单元格
    def sheetOneDimeScan(self, sheet='', cell_from='', row_pos=1, col_pos=1, direction=TExcelScanDire.kToRight, end_flag=None, tag_count=-1):
        '''
        从规定位置向单个方向扫描连续的单元格
        :param sheet: 表格名称
        :param cell_from: 起始单元格。可以写入''，直接指定坐标
        :param row_pos: 当cell_from=''时，生效。起始单元格行号
        :param col_pos: 当cell_from=''时，生效。起始单元格列号
        :param direction: 扫描方向
        :param end_flag: 结束标志
        :param tag_count: 最多扫描单元格个数，-1表示无上限
        :return: 扫描得到的单元格值列表
        '''
        result = []
        sh = self.sheet(sheet)

        if cell_from != '':
            cell = sh[cell_from]
            (row_pos, col_pos) = self.transRowColmun(cell_from)
        else:
            cell = sh.cell(row=row_pos, column=col_pos)

        while (cell.value != end_flag) and (tag_count < 0 or tag_count > len(result)):
            result.append(cell.value)

            if direction == TExcelScanDire.kToRight:
                col_pos += 1
            elif direction == TExcelScanDire.kToLeft:
                col_pos -= 1
                if col_pos == 0:
                    break
            elif direction == TExcelScanDire.kToUp:
                row_pos -= 1
                if row_pos == 0:
                    break
            else: # direction == TExcelScanDire.kToDown:
                row_pos += 1

            cell = sh.cell(row=row_pos, column=col_pos)

        return result

    ## 转换获取对应横纵坐标
    def transRowColmun(self, cell_prompt='A1'):
        # 实现坐标转换 后期换成手动计算
        row = 0
        column = 0
        col_flag = True

        for letter in cell_prompt:
            asc_val = ord(letter.upper())

            if col_flag:  # 转换列号
                if asc_val >= ord("A") and asc_val <= ord("Z"):
                    val = asc_val - ord("A") + 1
                    row = row * 26 + val
                elif asc_val >= ord("1") and asc_val <= ord("9"):
                    column = asc_val - ord("0")
                    col_flag = False
                else:
                    row = column = 0
                    break

            else:  # 转换行号
                if asc_val >= ord("0") and asc_val <= ord("9"):
                    val = asc_val - ord("0")
                    column = column * 10 + val
                    col_flag = False
                else:
                    row = column = 0
                    break

        return (row, column)

    ## 在某一行列查找指定单元格
    def findCell(self, tag_val: str, sheet='', cell_from='A1', direction=TExcelScanDire.kToRight, tag_count=-1):
        row = 0
        column = 0
        sh = self.sheet(sheet)
        cell = sh[cell_from]
        (row_pos, col_pos) = self.transRowColmun(cell_from)

        check_times = 0
        while tag_count < 0 or tag_count > check_times:

            if cell.value == tag_val:
                row = row_pos
                column = col_pos
                break
            else:
                check_times += 1

            if direction == TExcelScanDire.kToRight:
                col_pos += 1
            elif direction == TExcelScanDire.kToLeft:
                col_pos -= 1
                if col_pos == 0:
                    break
            elif direction == TExcelScanDire.kToUp:
                row_pos -= 1
                if row_pos == 0:
                    break
            else: # direction == TExcelScanDire.kToDown:
                row_pos += 1

            cell = sh.cell(row=row_pos, column=col_pos)

        return (row, column)